from typing import List, Tuple, Optional
from sqlalchemy.orm import Session
from sqlalchemy import func, extract, cast, Integer, and_, or_, case, select
from app.models.iaro_coop_admin.sponsor import Sponsor, SponsorContact, Sponsorship, SponsorshipPlaces
from app.models.iaro_coop_admin.program import Program
from app.models.coop_app.program_lookup import ProgramLookup
from app.models.coop_app.scholarship_amount import ScholarshipAmount

class SponsorsResearchRepo:
    def __init__(self, db: Session):
        self.db = db

    def count_sponsors_by_group(
        self,
        year_from: int,
        year_to:   int,
        only_current: Optional[bool],
        program_code: Optional[str],
        faculty:      Optional[str],
        coop_program: Optional[str],
        page:   int,
        page_size: int,
    ) -> Tuple[int, List[dict]]:
        # Status expression
        status_expr = case(
            (func.max(Sponsorship.year) > extract('year', func.sysdate()), "Current"),
            else_="Not Current"
        )

        q = (
            self.db.query(
                Program.program_code.label("program_code"),
                Program.start_year.label("program_year"),
                ProgramLookup.faculty.label("faculty"),
                ProgramLookup.co_op_program.label("coop_program"),
                status_expr.label("status"),
                func.count(func.distinct(Sponsorship.sponsor_id)).label("sponsor_count"),
            )
            .join(Sponsorship, Sponsorship.program_id == Program.program_id)
            .outerjoin(
                ProgramLookup,
                ProgramLookup.program_lookup_code == func.substr(Program.program_code, 1, 3)
            )
            # Program Year filter
            .filter(Program.start_year.between(year_from, year_to))
        )

        if program_code:
            q = q.filter(Program.program_code.ilike(f"%{program_code}%"))
        if faculty:
            q = q.filter(ProgramLookup.faculty == faculty)
        if coop_program:
            q = q.filter(ProgramLookup.co_op_program == coop_program)

        if only_current is True:
            q = q.having(status_expr == "Current")
        elif only_current is False:
            q = q.having(status_expr == "Not Current")

        q = q.group_by(
            Program.program_code,
            Program.start_year,
            ProgramLookup.faculty,
            ProgramLookup.co_op_program,
        )

        # Calculate total count
        subq = q.with_entities(
            Program.program_code,
            Program.start_year,
            ProgramLookup.faculty,
            ProgramLookup.co_op_program,
        ).subquery()
        total = self.db.query(func.count()).select_from(subq).scalar()

        # Get paginated results
        rows = (
            q.order_by(Program.program_code)
             .offset((page - 1) * page_size)
             .limit(page_size)
             .all()
        )

        return total, [r._asdict() for r in rows]


    def get_contacts_paginated(
        self,
        year_from:    int,
        year_to:      int,
        only_current: Optional[bool],
        program_code: Optional[str],
        faculty:      Optional[str],
        coop_program: Optional[str],
        page:         int,
        page_size:    int,
    ) -> Tuple[int, List[dict]]:

        status_expr = case(
            (func.max(Sponsorship.year) > extract('year', func.sysdate()), 'Current'),
            else_='Not Current'
        )

        q = (
            self.db.query(
                Sponsor.sponsor_name.label("company_name"),
                SponsorContact.sponsor_contact_id.label("sponsor_contact_id"),
                SponsorContact.title,
                SponsorContact.first_name,
                SponsorContact.last_name,
                SponsorContact.position_title,
                SponsorContact.phone,
                SponsorContact.mobile,
                SponsorContact.fax,
                SponsorContact.email,
                SponsorContact.website,
                SponsorContact.kc,
                SponsorContact.gm,
                SponsorContact.inv,
                SponsorContact.ss,
                SponsorContact.lk,
                SponsorContact.aa,
                SponsorContact.bb,
                SponsorContact.ec,
                SponsorContact.last_updated,
                SponsorContact.archive,
                SponsorContact.hidden,
                Program.program_code.label("program_code"),
                ProgramLookup.faculty,
                ProgramLookup.co_op_program.label("coop_program"),
                Program.start_year.label("program_year"),
                func.listagg(func.distinct(Sponsorship.year), ', ')
                    .within_group(Sponsorship.year).label("scholarship_years"),
                func.max(Sponsorship.year).label("latest_year"),
                status_expr.label("status"),
            )
            # Sponsor → Contact (only KC='Y' or INV='Y')
            .outerjoin(
                SponsorContact,
                and_(
                    SponsorContact.sponsor_id == Sponsor.sponsor_id,
                    or_(SponsorContact.kc == 'Y', SponsorContact.inv == 'Y')
                )
            )
            .outerjoin(Sponsorship, Sponsorship.sponsor_id == Sponsor.sponsor_id)
            .outerjoin(Program, Program.program_id == Sponsorship.program_id)
            .outerjoin(
                ProgramLookup,
                ProgramLookup.program_lookup_code == func.substr(Program.program_code, 1, 3)
            )
            .filter(Program.start_year.between(year_from, year_to))
        )

        if program_code:
            q = q.filter(Program.program_code.ilike(f"%{program_code}%"))
        if faculty:
            q = q.filter(ProgramLookup.faculty == faculty)
        if coop_program:
            q = q.filter(ProgramLookup.co_op_program == coop_program)

        if only_current is True:
            q = q.having(status_expr == 'Current')
        elif only_current is False:
            q = q.having(status_expr == 'Not Current')

        q = q.group_by(
            Sponsor.sponsor_name,
            SponsorContact.sponsor_contact_id,
            SponsorContact.title,
            SponsorContact.first_name,
            SponsorContact.last_name,
            SponsorContact.position_title,
            SponsorContact.phone,
            SponsorContact.mobile,
            SponsorContact.fax,
            SponsorContact.email,
            SponsorContact.website,
            SponsorContact.kc,
            SponsorContact.gm,
            SponsorContact.inv,
            SponsorContact.ss,
            SponsorContact.lk,
            SponsorContact.aa,
            SponsorContact.bb,
            SponsorContact.ec,
            SponsorContact.last_updated,
            SponsorContact.archive,
            SponsorContact.hidden,
            Program.program_code,
            Program.start_year,
            ProgramLookup.faculty,
            ProgramLookup.co_op_program,
        )

        total = q.count()
        rows = (
            q.order_by(Sponsor.sponsor_name, Program.start_year)
             .offset((page - 1) * page_size)
             .limit(page_size)
             .all()
        )

        return total, [r._asdict() for r in rows]

    def sum_scholarships_by_company(
        self,
        year_from: int,
        year_to: int,
        only_current: Optional[bool],
        program_code: Optional[str],
        page: int,
        page_size: int,
    ) -> Tuple[int, List[dict]]:
        # 4-year total amount subquery (based on p.START_YEAR)
        total_amount_sq = (
            select(func.sum(ScholarshipAmount.amount))
            .where(
                ScholarshipAmount.year.between(
                    Program.start_year,
                    Program.start_year + 3
                )
            )
            .scalar_subquery()
        )

        # Status expression: based on MAX(sp.YEAR)
        status_expr = case(
            (func.max(Sponsorship.year) > extract('year', func.sysdate()), "Current"),
            else_="Not Current"
        )

        q = (
            self.db.query(
                Sponsor.sponsor_name.label("company_name"),
                Program.program_code.label("program_code"),
                Program.start_year.label("program_year"),
                (func.count(SponsorshipPlaces.sponsorship_place_id) / 4).label("position"),
                total_amount_sq.label("total_amount_per_position"),
                ((func.count(SponsorshipPlaces.sponsorship_place_id) / 4) * total_amount_sq)
                    .label("sponsorship_cost"),
                status_expr.label("status"),
            )
            .join(Sponsorship, Sponsorship.sponsor_id == Sponsor.sponsor_id)
            .join(Program, Program.program_id == Sponsorship.program_id)
            .join(SponsorshipPlaces, SponsorshipPlaces.sponsorship_id == Sponsorship.sponsorship_id)
            # Year range filter
            .filter(Program.start_year.between(year_from, year_to))
        )

        if program_code:
            q = q.filter(Program.program_code.ilike(f"%{program_code}%"))

        # Status filter
        if only_current is True:
            q = q.having(status_expr == "Current")
        elif only_current is False:
            q = q.having(status_expr == "Not Current")

        # Group by
        q = q.group_by(
            Sponsor.sponsor_name,
            Program.program_code,
            Program.start_year,
        )

        # Total count: build subquery then count
        subq = q.with_entities(
            Sponsor.sponsor_name,
            Program.program_code,
            Program.start_year
        ).subquery()
        total = self.db.query(func.count()).select_from(subq).scalar()

        # Get paginated data
        rows = (
            q
            .order_by(Sponsor.sponsor_name, Program.program_code)
            .offset((page - 1) * page_size)
            .limit(page_size)
            .all()
        )

        return total, [r._asdict() for r in rows]
    
    def sum_scholarships_by_program(
        self,
        year_from: int,
        year_to: int,
        only_current: Optional[bool],
        program_code: Optional[str],
        page: int,
        page_size: int,
    ) -> Tuple[int, List[dict]]:
        # Subquery: 4-year total amount
        total_amount_sq = (
            select(func.sum(ScholarshipAmount.amount))
            .where(
                ScholarshipAmount.year.between(
                    Program.start_year,
                    Program.start_year + 3
                )
            )
            .scalar_subquery()
        )

        # CASE expression: Current / Not Current
        status_expr = case(
            (func.max(Sponsorship.year) > extract('year', func.sysdate()), "Current"),
            else_="Not Current"
        )

        q = (
            self.db.query(
                Program.program_code.label("program_code"),
                Program.start_year.label("program_year"),
                ProgramLookup.faculty.label("faculty"),
                ProgramLookup.co_op_program.label("coop_program"),
                (func.count(func.distinct(Sponsorship.sponsor_id))).label("sponsor_count"),
                cast(
                    func.count(SponsorshipPlaces.sponsorship_place_id) / 4,
                    Integer
                ).label("position"),
                total_amount_sq.label("total_amount_per_position"),
                (
                    (func.count(SponsorshipPlaces.sponsorship_place_id) / 4) * total_amount_sq
                ).label("sponsorship_cost"),
                status_expr.label("status"),
            )
            .join(Sponsorship, Sponsorship.program_id == Program.program_id)
            .join(Sponsor, Sponsor.sponsor_id == Sponsorship.sponsor_id)  
            .join(SponsorshipPlaces, SponsorshipPlaces.sponsorship_id == Sponsorship.sponsorship_id)
            .outerjoin(
                ProgramLookup,
                ProgramLookup.program_lookup_code == func.substr(Program.program_code, 1, 3)
            )
            # Only consider Program Year in range
            .filter(Program.start_year.between(year_from, year_to))
        )

        if program_code:
            q = q.filter(Program.program_code.ilike(f"%{program_code}%"))

        # Status filter
        if only_current is True:
            q = q.having(status_expr == "Current")
        elif only_current is False:
            q = q.having(status_expr == "Not Current")

        q = q.group_by(
            Program.program_code,
            Program.start_year,
            ProgramLookup.faculty,
            ProgramLookup.co_op_program,
        )

        # Get total count
        subq = q.with_entities(
            Program.program_code,
            Program.start_year,
            ProgramLookup.faculty,
            ProgramLookup.co_op_program,
        ).subquery()
        total = self.db.query(func.count()).select_from(subq).scalar()

        # Get paginated details
        rows = (
            q.order_by(Program.start_year, Program.program_code)
             .offset((page - 1) * page_size)
             .limit(page_size)
             .all()
        )
        return total, [r._asdict() for r in rows]